/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sample;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author dthanh
 */
public class Schedules {

    private ArrayList<Schedule> scheduleList;
    public Schedules() {
        scheduleList = new ArrayList();
    }

    //hoa implement function nay nhe ^^
    //get student who registered to receive email today
    public ArrayList getCurrentStudentID() {
        ArrayList list = new ArrayList();
        return list;
    }
    public ArrayList<Schedule> getList(){
        return scheduleList;
    }

    public boolean readSchedules(Date curDate) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        //get list student
        ArrayList list = new ArrayList();
        //get parameter for SELECT QUERY
        String selectParam = "";
        for (int i=0;i<list.size();i++) {
            selectParam += "'" + list.toString() + "'";
            if (i<list.size() - 1)
                selectParam += ",";
        }
        try {
            con = getConnection();
            String sql = String.format("SELECT * FROM Schedules WHERE StartTime>\'%d/%d/%d\'"
                  ,curDate.getMonth()+1,curDate.getDate(),curDate.getYear()+1900);
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                Student student = getStudentByID(rs.getString("StudentID"));
                Subject subject = getSubjectByID(rs.getInt("Subject"));
                Date startTime = rs.getDate("StartTime");
                Date endTime = rs.getDate("EndTime");
                String room = rs.getString("Room");
                String seat = rs.getString("Seat");
                String examType = rs.getString("ExamType");
                String note = rs.getString("Note");
                Schedule tmp = new Schedule(student, subject, startTime,
                        endTime, room, seat, examType, note);
                scheduleList.add(tmp);
                System.out.println(student.getStName());
            }
        } catch (Exception ex) {
            System.out.println("Error occurs: "+ex.getMessage());
            ex.printStackTrace();
            return false;
        } finally {
            try {
                if (rs!=null) {
                    rs.close();
                }
                if (stm!=null) {
                    stm.close();
                }
                if (con!=null) {
                    con.close();
                }
            } catch (Exception ex) {
                System.out.println("Error occurs: "+ex.getMessage());
                ex.printStackTrace();
                return false;
            }
        }
        return true;
    }

    public Subject getSubjectByID(int subID) {
        Subject subject = null;
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            String sql = "SELECT * FROM Subjects WHERE ID=" + subID;
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            if (rs.next()) {
                String subName = rs.getString("SubjectName");
                String fullSubName = rs.getString("Type");
                String type = rs.getString("FullName");
                subject = new Subject(subName, fullSubName, type);
            }
        } catch (Exception ex) {
            System.out.println("Error occurs: "+ex.getMessage());
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs!=null) {
                    rs.close();
                }
                if (stm!=null) {
                    stm.close();
                }
                if (con!=null) {
                    con.close();
                }
            } catch (Exception ex) {
                System.out.println("Error occurs: "+ex.getMessage());
                ex.printStackTrace();
            }
        }
        return subject;
    }

    public Student getStudentByID(String stID) {
        Student student = null;
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            String sql = "SELECT * FROM Students WHERE ID='" + stID + "'";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            if (rs.next()) {
                String fullName = rs.getString("Fullname");
                Date dob = rs.getDate("DOB");
                student = new Student(stID, fullName, dob);
            }
        } catch (Exception ex) {
            System.out.println("Error occurs: "+ex.getMessage());
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs!=null) {
                    rs.close();
                }
                if (stm!=null) {
                    stm.close();
                }
                if (con!=null) {
                    con.close();
                }
            } catch (Exception ex) {
                System.out.println("Error occurs: "+ex.getMessage());
                ex.printStackTrace();
            }
        }
        return student;
    }

    public Connection getConnection() {
        Connection con = null;
        try {
            String current = "";
            String server = "";
            String port = "";
            String dbName = "";
            String instance = "";
            String user = "";
            String pwd = "";
            BufferedReader br = new BufferedReader(new FileReader("connection.txt"));
            // Get connection information
            while ((current = br.readLine()) != null) {
                if (current.indexOf(":") != -1) {
                    if (current.substring(0,current.indexOf(":")).trim()
                            .equals("server")) {
                        server = current.substring(current.indexOf(":")+1,current
                                .trim().length()).trim();
                    }
                    if (current.substring(0,current.indexOf(":")).trim()
                            .equals("port")) {
                        port = current.substring(current.indexOf(":")+1,current
                                .trim().length()).trim();
                    }
                    if (current.substring(0,current.indexOf(":")).trim()
                            .equals("databaseName")) {
                        dbName = current.substring(current.indexOf(":")+1,current
                                .trim().length()).trim();
                    }
                    if (current.substring(0,current.indexOf(":")).trim()
                            .equals("instanceName")) {
                        instance = current.substring(current.indexOf(":")+1,current
                                .trim().length()).trim();
                    }
                    if (current.substring(0,current.indexOf(":")).trim()
                            .equals("user")) {
                        user = current.substring(current.indexOf(":")+1,current
                                .trim().length()).trim();
                    }
                    if (current.substring(0,current.indexOf(":")).trim()
                            .equals("password")) {
                        pwd = current.substring(current.indexOf(":")+1,current
                                .trim().length()).trim();
                    }
                }
            }
            // Create connection
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String conStr = String.format("jdbc:sqlserver://%s:%s;databaseName=%s"
                   +";instanceName=%s",server,port,dbName,instance);
            con = DriverManager.getConnection(conStr,user,pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }
}
